from datetime import datetime

import customtkinter as ctk
from tkinter import filedialog
from tkinter import messagebox

import openpyxl
import pandas as pd
import os
from decimal import Decimal, InvalidOperation
from src.core.file_processing.report_writer import create_report_from_code
from src.core.data_processing.csv_handler import load_company_accounts_from_package


class BankReconciliationView(ctk.CTkFrame):
    def __init__(self, master):
        # ... [__init__ 方法和其他UI函数保持不变] ...
        super().__init__(master, fg_color="transparent")
        # 从文本配置加载公司与账号信息
        self.company_map = {}  # { company_name: { 'sheet_name': str, 'accounts': [ {'bank_name','account_number'} ] } }
        self.company_names = []
        self.company_to_accounts = {}  # { company_name: [account_number, ...] }
        self.company_sheet_name = {}  # { company_name: sheet_name }
        self._load_company_config()
        self.grid_columnconfigure(1, weight=1)
        title_label = ctk.CTkLabel(self, text="银行存款余额调节表生成", font=ctk.CTkFont(size=20, weight="bold"))
        title_label.grid(row=0, column=0, columnspan=3, padx=10, pady=(10, 20), sticky="w")
        company_label = ctk.CTkLabel(self, text="编制单位:")
        company_label.grid(row=1, column=0, padx=(10, 5), pady=10, sticky="e")
        company_display_names = self.company_names
        self.company_combobox = ctk.CTkComboBox(self, values=company_display_names, command=self.on_company_select)
        self.company_combobox.grid(row=1, column=1, columnspan=2, padx=5, pady=10, sticky="ew")
        self.company_combobox.set("请选择...")
        account_label = ctk.CTkLabel(self, text="银行账号:")
        account_label.grid(row=2, column=0, padx=(10, 5), pady=10, sticky="e")
        self.account_combobox = ctk.CTkComboBox(self, values=[], command=self.on_account_select, state="disabled")
        self.account_combobox.grid(row=2, column=1, columnspan=2, padx=5, pady=10, sticky="ew")
        self.account_combobox.set("请先选择编制单位...")
        # 开户行显示标签
        self.bank_name_label = ctk.CTkLabel(self, text="开户行：", anchor="w", justify="left")
        self.bank_name_label.grid(row=3, column=0, columnspan=3, padx=(10, 5), pady=(0, 10), sticky="w")
        section_label = ctk.CTkLabel(self, text="文件导入与生成", font=ctk.CTkFont(size=16, weight="bold"))
        section_label.grid(row=4, column=0, columnspan=3, padx=10, pady=(20, 10), sticky="w")
        ledger_label = ctk.CTkLabel(self, text="公司明细账:")
        ledger_label.grid(row=5, column=0, padx=(10, 5), pady=10, sticky="e")
        self.ledger_path_entry = ctk.CTkEntry(self, placeholder_text="请选择公司银行存款明细账文件...")
        self.ledger_path_entry.grid(row=5, column=1, padx=5, pady=10, sticky="ew")
        self.ledger_select_button = ctk.CTkButton(self, text="选择文件", width=100, command=self.select_ledger_file)
        self.ledger_select_button.grid(row=5, column=2, padx=5, pady=10)
        statement_label = ctk.CTkLabel(self, text="银行对账单:")
        statement_label.grid(row=6, column=0, padx=(10, 5), pady=10, sticky="e")
        self.statement_path_entry = ctk.CTkEntry(self, placeholder_text="请选择银行交易明细文件...")
        self.statement_path_entry.grid(row=6, column=1, padx=5, pady=10, sticky="ew")
        self.statement_select_button = ctk.CTkButton(self, text="选择文件", width=100,
                                                    command=self.select_statement_file)
        self.statement_select_button.grid(row=6, column=2, padx=5, pady=10)
        template_label = ctk.CTkLabel(self, text="调节表生成:")
        template_label.grid(row=7, column=0, padx=(10, 5), pady=10, sticky="e")
        self.template_path_entry = ctk.CTkEntry(self, placeholder_text="请选择要写入的调节表文件...")
        self.template_path_entry.grid(row=7, column=1, padx=5, pady=10, sticky="ew")
        self.template_select_button = ctk.CTkButton(self, text="选择文件", width=100, command=self.select_template_file)
        self.template_select_button.grid(row=7, column=2, padx=5, pady=10)
        self.generate_button = ctk.CTkButton(self, text="开始生成调节表", font=ctk.CTkFont(size=14, weight="bold"),
                                            command=self.start_reconciliation)
        self.generate_button.grid(row=8, column=0, columnspan=3, padx=10, pady=(20, 10), sticky="ew")
        self.status_label = ctk.CTkLabel(self, text="状态: 准备就绪", anchor="w", justify="left")
        self.status_label.grid(row=9, column=0, columnspan=3, padx=10, pady=10, sticky="ew")
        self.bind("<Configure>", self._on_resize)

    def _on_resize(self, event):
        self.status_label.configure(wraplength=event.width - 20)

    def on_company_select(self, selected_name):
        self.account_combobox.set("请选择银行账号...")
        if selected_name not in self.company_to_accounts:
            self.account_combobox.configure(values=[], state="disabled")
            self.account_combobox.set("请先选择编制单位...")
            return
        account_display_numbers = self.company_to_accounts.get(selected_name, [])
        if account_display_numbers:
            self.account_combobox.configure(values=account_display_numbers, state="normal")
            self.account_combobox.set("请选择银行账号...")
            self.bank_name_label.configure(text="开户行：")
        else:
            self.account_combobox.configure(values=[], state="disabled")
            self.account_combobox.set("该公司无可用账号")
            self.bank_name_label.configure(text="开户行：")
            messagebox.showinfo(title="提示", message=f"您选择的单位 “{selected_name}” 没有关联的银行账号。")

    def on_account_select(self, selected_account):
        company_name = self.company_combobox.get()
        bank_name = self._lookup_bank_name(company_name, selected_account)
        if bank_name:
            self.bank_name_label.configure(text=f"开户行：{bank_name}")
            print(f"选择了账号: '{selected_account}', 开户行: '{bank_name}'")
        else:
            self.bank_name_label.configure(text="开户行：")

    def _select_file(self, entry_widget):
        file_path = filedialog.askopenfilename(title="选择Excel文件",
                                               filetypes=[("Excel Files", "*.xlsx *.xls"), ("All files", "*.*")])
        if file_path:
            if file_path.lower().endswith(('.xlsx', '.xls')):
                entry_widget.delete(0, "end")
                entry_widget.insert(0, file_path)
                print(f"选择了有效的Excel文件: {file_path}")
                self.status_label.configure(text="状态: 准备就绪")
            else:
                messagebox.showerror(title="文件类型错误",
                                     message="您选择的不是有效的Excel文件。\n请确保文件后缀是 .xlsx 或 .xls。")
                print(f"拒绝了无效的文件类型: {file_path}")

    def select_ledger_file(self):
        self._select_file(self.ledger_path_entry)

    def select_statement_file(self):
        self._select_file(self.statement_path_entry)

    def select_template_file(self):
        self._select_file(self.template_path_entry)

    def start_reconciliation(self):
        """主操作函数，调用精确复刻图片的报告生成逻辑"""
        try:
            # 1. 获取文件路径
            ledger_file = self.ledger_path_entry.get()
            statement_file = self.statement_path_entry.get()
            template_file = self.template_path_entry.get()

            # 检查必要的输入文件
            if not all([ledger_file, statement_file]):
                self.status_label.configure(text="状态: 错误！请确保\"公司明细账\"和\"银行对账单\"都已选择。",
                                            text_color="red")
                return

            self.status_label.configure(text="状态: 开始解析文件...", text_color="orange")
            self.update_idletasks()

            # 2. 解析文件
            ledger_data = self.parse_ledger_file(ledger_file)
            statement_data = self.parse_statement_file(statement_file)

            # 3. 对账
            if ledger_data['balance'] == statement_data['balance']:
                self.status_label.configure(text="状态: 对账成功！正在处理调节表文件...", text_color="blue")
                self.update_idletasks()

                # a. 从UI和解析结果中收集所有需要的数据
                company_name_from_ui = self.company_combobox.get()
                account_number_from_ui = self.account_combobox.get()
                bank_name_hidden = self._lookup_bank_name(company_name_from_ui, account_number_from_ui) or "未知开户行"
                sheet_name_for_company = self.company_sheet_name.get(company_name_from_ui, "Sheet1")

                report_data = {
                    'company_name': company_name_from_ui,
                    'account_number': account_number_from_ui,
                    'bank_name': bank_name_hidden,
                    'balance': ledger_data['balance']
                }

                # b. 处理调节表文件
                if template_file:
                    # 如果选择了文件，先备份原文件
                    self.status_label.configure(text="状态: 正在备份原文件...", text_color="blue")
                    self.update_idletasks()
                    
                    backup_file = self._backup_file(template_file)
                    if backup_file:
                        self.status_label.configure(text=f"状态: 原文件已备份为: {os.path.basename(backup_file)}", text_color="blue")
                        self.update_idletasks()
                    
                    # 追加对账内容到现有文件
                    self.status_label.configure(text="状态: 正在追加对账内容...", text_color="blue")
                    self.update_idletasks()
                    
                    from src.core.file_processing.report_writer import append_reconciliation_to_file
                    append_reconciliation_to_file(template_file, sheet_name_for_company, report_data)
                    
                    success_message = f"状态: 操作完成！\n对账成功，已追加到文件:\n{template_file}"
                    self.status_label.configure(text=success_message, text_color="green")
                    print("追加到现有文件成功。")
                    
                else:
                    # 如果没有选择文件，提示会新建文件
                    result = messagebox.askyesno("文件选择", 
                                               "您没有选择调节表生成文件。\n\n是否要新建一个调节表文件？\n\n点击\"是\"将创建新文件，点击\"否\"将取消操作。")
                    
                    if result:
                        # 创建新文件
                        self.status_label.configure(text="状态: 正在创建新的调节表文件...", text_color="blue")
                        self.update_idletasks()
                        
                        # 获取公司明细账文件的目录
                        ledger_file = self.ledger_path_entry.get()
                        ledger_directory = os.path.dirname(ledger_file) if ledger_file else os.getcwd()
                        
                        # 构建输出文件名（保存在明细账文件同目录）
                        timestamp = datetime.now().strftime('%Y%m月%d日_%H%M%S')
                        output_filename = f"银行存款余额调节表_{report_data['company_name']}_{timestamp}.xlsx"
                        output_path = os.path.join(ledger_directory, output_filename)
                        
                        # 创建全新的Excel工作簿
                        workbook = openpyxl.Workbook()
                        sheet = workbook.active
                        sheet.title = sheet_name_for_company
                        
                        # 调用创建函数，在第一行开始创建并填充数据
                        create_report_from_code(sheet, start_row=1, report_data=report_data)
                        
                        # 保存这个在内存中创建的新文件
                        workbook.save(output_path)
                        
                        success_message = f"状态: 操作完成！\n对账成功，已生成新的报告文件:\n{output_path}"
                        self.status_label.configure(text=success_message, text_color="green")
                        print("新建文件成功。")
                    else:
                        self.status_label.configure(text="状态: 操作已取消", text_color="orange")
                        return

            else:
                # 对账失败的逻辑保持不变
                difference = abs(ledger_data['balance'] - statement_data['balance'])
                error_message = (
                    f"状态: 对账不成功，余额不匹配！\n账面余额: {ledger_data['balance']:,.2f}\n银行余额: {statement_data['balance']:,.2f}\n差额为: {difference:,.2f}")
                self.status_label.configure(text=error_message, text_color="red")
                messagebox.showwarning("对账失败",
                                       f"余额不匹配，请手工检查！\n\n账面余额: {ledger_data['balance']:,.2f}\n银行余额: {statement_data['balance']:,.2f}\n差    额: {difference:,.2f}")

        except Exception as e:
            error_message = f"状态: 错误！{e}"
            self.status_label.configure(text=error_message, text_color="red")
            print(f"解析失败: {e}")

    def _load_company_config(self):
        try:
            companies = load_company_accounts_from_package()
            self.company_map = companies
            self.company_names = list(companies.keys())
            for company, meta in companies.items():
                self.company_sheet_name[company] = meta.get('sheet_name') or 'Sheet1'
                accounts = meta.get('accounts', [])
                self.company_to_accounts[company] = [acc.get('account_number', '') for acc in accounts if acc.get('account_number')]
        except Exception as e:
            print(f"加载公司配置失败: {e}")
            self.company_map = {}
            self.company_names = []
            self.company_to_accounts = {}
            self.company_sheet_name = {}

    def _lookup_bank_name(self, company_name: str, account_number: str) -> str | None:
        meta = self.company_map.get(company_name)
        if not meta:
            return None
        for acc in meta.get('accounts', []):
            if acc.get('account_number') == account_number:
                return acc.get('bank_name')
        return None

    def _str_to_decimal(self, value: any) -> Decimal:
        # ... _str_to_decimal 函数保持不变 ...
        try:
            return Decimal(str(value).replace(',', ''))
        except (InvalidOperation, ValueError, TypeError):
            raise ValueError(f"无法将值“{value}”转换为有效的数字。")

    def parse_ledger_file(self, file_path: str) -> dict:
        # ... parse_ledger_file 函数保持不变 ...
        df_full = None
        try:
            df_full = pd.read_excel(file_path, sheet_name='明细账', header=None)
        except Exception:
            encodings_to_try = ['utf-8', 'utf-8-sig', 'gbk', 'gb18030']
            for encoding in encodings_to_try:
                try:
                    df_full = pd.read_csv(file_path, header=None, encoding=encoding)
                    break
                except Exception:
                    continue
        if df_full is None: raise ValueError("无法将明细账文件作为Excel或CSV格式打开。")
        company_name, account_name = "未找到", "未找到"
        header_row_1 = df_full.iloc[1]
        for cell in header_row_1:
            cell_str = str(cell)
            if '编制单位' in cell_str: company_name = cell_str.split('：', 1)[-1].strip() if '：' in cell_str else \
            cell_str.split(':', 1)[-1].strip()
            if '科目' in cell_str: account_name = cell_str.split('：', 1)[-1].strip() if '：' in cell_str else \
            cell_str.split(':', 1)[-1].strip()
        df_body = df_full[3:].copy()
        df_body.columns = df_full.iloc[2]
        target_rows = df_body[df_body['摘要'].astype(str).str.strip() == '本期合计']
        if target_rows.empty: raise ValueError("在明细账中没有找到“摘要”为“本期合计”的行。")
        final_balance = self._str_to_decimal(target_rows['余额'].iloc[0])
        result = {"company_name": company_name, "account_name": account_name, "balance": final_balance}
        print(f"明细账解析完成: {result}")
        return result

    def parse_statement_file(self, file_path: str) -> dict:
        # ... parse_statement_file 函数保持不变 ...
        print("\n--- 开始解析银行对账单 (自动寻找表头) ---")
        df_full = None
        try:
            df_full = pd.read_excel(file_path, header=None)
        except Exception:
            encodings_to_try = ['utf-8', 'utf-8-sig', 'gbk', 'gb18030']
            for encoding in encodings_to_try:
                try:
                    df_full = pd.read_csv(file_path, header=None, encoding=encoding)
                    print(f"文件成功以 {encoding} 编码的CSV格式读取。")
                    break
                except Exception:
                    continue
        if df_full is None: raise ValueError("无法将银行对账单文件作为Excel或CSV格式打开。")
        header_row_index = -1
        for i, row in df_full.head(10).iterrows():
            row_values = set(str(v) for v in row.values)
            if '交易时间' in row_values and '余额' in row_values:
                header_row_index = i
                print(f"在第 {i + 1} 行找到表头。")
                break
        if header_row_index == -1: raise ValueError("在银行对账单文件中找不到包含'交易时间'和'余额'的表头行。")
        df_body = df_full[header_row_index + 1:].copy()
        df_body.columns = df_full.iloc[header_row_index]
        df_body.reset_index(drop=True, inplace=True)
        required_columns = ['交易时间', '余额']
        if not all(col in df_body.columns for col in required_columns): raise ValueError(
            f"银行对账单文件中缺少必要的列: {required_columns}")
        df_body.dropna(subset=required_columns, inplace=True)
        if df_body.empty: raise ValueError("对账单中没有找到包含“交易时间”和“余额”的有效记录。")
        df_body['交易时间'] = pd.to_datetime(df_body['交易时间'], errors='coerce')
        df_body.dropna(subset=['交易时间'], inplace=True)
        if df_body.empty: raise ValueError("对账单中没有找到有效的日期。")
        latest_row = df_body.loc[df_body['交易时间'].idxmax()]
        final_balance = self._str_to_decimal(latest_row['余额'])
        result = {"balance": final_balance, "filename": os.path.basename(file_path)}
        print(f"银行对账单解析完成: {result}")
        return result

    def _backup_file(self, file_path: str) -> str:
        """备份文件，在文件名后添加时间戳"""
        try:
            if not os.path.exists(file_path):
                return None
                
            # 获取文件路径和扩展名
            directory = os.path.dirname(file_path)
            filename = os.path.basename(file_path)
            name, ext = os.path.splitext(filename)
            
            # 生成时间戳
            timestamp = datetime.now().strftime('%Y%m%d_%H%M%S')
            
            # 构建备份文件名
            backup_filename = f"{name}_备份_{timestamp}{ext}"
            backup_path = os.path.join(directory, backup_filename)
            
            # 复制文件
            import shutil
            shutil.copy2(file_path, backup_path)
            
            print(f"文件已备份: {backup_path}")
            return backup_path
            
        except Exception as e:
            print(f"备份文件失败: {e}")
            return None